from common import CommitteeMemberEntity,SearchFilter
import DatabaseConnection

class CommitteeMemberData(object):
    def __init__(self):
        pass

    def InsertCommitteeMember(self,committeeMember):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsCommitteeMember',(committeeMember.CommitteeID,committeeMember.MemberID,committeeMember.StartDate,committeeMember.EndDate,committeeMember.Active))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateCommitteeMember(self,committeeMember):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdCommitteeMember',(committeeMember.CommitteeID,committeeMember.MemberID,committeeMember.StartDate,committeeMember.EndDate,committeeMember.Active))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteCommitteeMember(self,cID,mID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelCommitteeMember',(cID,mID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type CommitteeMemberEntity or None (if there is no record with this ID)
    def SelectCommitteeMemberByID(self,cID,mID):
        query = """ SELECT CommitteeID,MemberID,StartDate,EndDate,Active,MemberName
                    FROM vwCommitteeMember
                    WHERE CommitteeID='%s' AND MemberID=mID""" % (cID,mID)

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return CommitteeMemberEntity.CommitteeMemberEntity(CommitteeID=result[0][0],MemberID=result[0][1],StartDate=result[0][2],EndDate=result[0][3],Active=result[0][4])
        else:
            return None

    #This method returns a list of objects of type CommitteeMemberEntity
    def SelectCommitteeMemberByFilter(self,searchFilter):
        query = """ SELECT CommitteeID,MemberID,StartDate,EndDate,Active,MemberName
                    FROM vwCommitteeMember"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(CommitteeMemberEntity.CommitteeMemberEntity(CommitteeID=record[0],MemberID=record[1],StartDate=record[2],EndDate=record[3],Active=record[4]))
        return recordSet

    def SelectAllCommitteeMembers(self):
        query = """ SELECT CommitteeID,MemberID,StartDate,EndDate,Active,MemberName
                    FROM vwCommitteeMember"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(CommitteeMemberEntity.CommitteeMemberEntity(CommitteeID=record[0],MemberID=record[1],StartDate=record[2],EndDate=record[3],Active=record[4]))
        return recordSet
